﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Gedea.Model.Entities;
using System.Data;
using System.Data.SqlClient;
using Gedea.Model;
using Gedea.Core.Manager;
using Gedea.Core.Manager.DataAccess;

namespace Gedea.Core.Repositories
{
    public class CustomerRepository
    {
        public void Add(Customer customer)
        {
            using (SqlConnection conn = new SqlConnection(DataConnection.GetConnection()))
            {
                string statement =
                    string.Format("INSERT INTO Customers ({0}, {1}, {2}, {3}, {4}) VALUES (?, ?, ?, ?, ?)"
                    , DBObjects.Customers.realName.ToString()
                    , DBObjects.Customers.idForBank.ToString()
                    , DBObjects.Customers.cbu.ToString()
                    , DBObjects.Customers.areaId.ToString()
                    , DBObjects.Customers.userId.ToString());

                SqlCommand command = new SqlCommand(statement, conn);

                command.Parameters.Add(DBObjects.Customers.realName.ToString(), SqlDbType.VarChar).Value = customer.RealName;
                command.Parameters.Add(DBObjects.Customers.idForBank.ToString(), SqlDbType.VarChar).Value = customer.IdForBank;
                command.Parameters.Add(DBObjects.Customers.cbu.ToString(), SqlDbType.VarChar).Value = customer.Cbu;
                command.Parameters.Add(DBObjects.Customers.areaId.ToString(), SqlDbType.VarChar).Value = customer.AreaId;
                command.Parameters.Add(DBObjects.Customers.userId.ToString(), SqlDbType.VarChar).Value = customer.UserId;

                DataConnection.Open(conn);

                command.ExecuteNonQuery();
            }
        }

        public Customer Get(int id)
        {
            string whereClause =
                string.Format("WHERE {0} = {1}"
                , DBObjects.Customers.customerId.ToString()
                , id);

            return new DataLookFor().Get<Customer>(whereClause);
        }

        public List<Customer> ListBy(DBObjects.Customers field, string q)
        {
            string query =
                string.Format("SELECT * FROM Customers WHERE {0} = {1} ORDER BY {0}"
                , field.ToString()
                , q);

            return new DataLookFor().ListEntity<Customer>(query);
        }

        public List<Customer> List(User user)
        {
            string query =
                string.Format("SELECT * FROM Customers WHERE {0} = {1}  ORDER BY {2}"
                , DBObjects.Customers.userId.ToString()
                , user.UserId
                , DBObjects.Customers.realName.ToString());

            return new DataLookFor().ListEntity<Customer>(query);
        }

        public void Update(Customer customer)
        {
            using (SqlConnection conn = new SqlConnection(DataConnection.GetConnection()))
            {
                string statement =
                    string.Format("UPDATE Customers SET {0} = ?, {1} = ?, {2} = ?, {3} = ?, {4} = ? WHERE {5} = ?;"
                    , DBObjects.Customers.realName.ToString()
                    , DBObjects.Customers.idForBank.ToString()
                    , DBObjects.Customers.cbu.ToString()
                    , DBObjects.Customers.areaId.ToString()
                    , DBObjects.Customers.userId.ToString()
                    , DBObjects.Customers.customerId.ToString());

                SqlCommand command = new SqlCommand(statement, conn);

                command.Parameters.Add(DBObjects.Customers.realName.ToString(), SqlDbType.VarChar).Value = customer.RealName;
                command.Parameters.Add(DBObjects.Customers.idForBank.ToString(), SqlDbType.VarChar).Value = customer.IdForBank;
                command.Parameters.Add(DBObjects.Customers.cbu.ToString(), SqlDbType.VarChar).Value = customer.Cbu;
                command.Parameters.Add(DBObjects.Customers.areaId.ToString(), SqlDbType.VarChar).Value = customer.AreaId;
                command.Parameters.Add(DBObjects.Customers.userId.ToString(), SqlDbType.VarChar).Value = customer.UserId;
                command.Parameters.Add(DBObjects.Customers.customerId.ToString(), SqlDbType.VarChar).Value = customer.CustomerId;


                DataConnection.Open(conn);

                command.ExecuteNonQuery();
            } 
        }

        private Customer ConvertToCustomer(IDataReader dr)
        {
            return new DataLookFor().MapToEntity<Customer>(dr, false);
        }
    }
}
